﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DemoPager
{
    public partial class frmMain : Form
    {
        public frmMain()
        {
            InitializeComponent();
        }

        #region Members
        //总记录数
        public int RecordCount = 0;
        private string strConn = @"Data Source=127.0.0.1;Initial Catalog=Db_TonyPaging;Integrated Security=SSPI;";
        //"Server=localhost;database=Db_TonyPaging;uid=sa;pwd=sa;";
        private string strProcedure = "ZJF_CPP_GetPagedRecordFor2005_2008";
        #endregion

        #region Methods

        /// <summary>
        /// 绑定第Index页的数据
        /// </summary>
        /// <param name="Index"></param>
        private void BindDataWithPage(int Index)
        {
            winFormPager1.PageIndex = Index;
            //winFormPager1.PageSize = 10;; ;
            dgvList.DataSource = GetData(strConn, strProcedure, Index, winFormPager1.PageSize);
            //获取并设置总记录数
            winFormPager1.RecordCount = RecordCount;
        }


        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="strProcedure"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        private DataTable GetData(string conn, string strProcedure, int pageIndex, int pageSize)
        {

            using (SqlConnection connection = new SqlConnection(conn))
            {
                SqlCommand command = new SqlCommand(strProcedure, connection);
                command.CommandType = CommandType.StoredProcedure;//采用存储过程
                //存储过程参数
                command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = "DepartDemo";
                command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "PKID";
                command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = "*";
                command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = " 1=1 ";
                command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();
                command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();
                command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = " PKID desc";
                //打开连接
                if (connection.State != ConnectionState.Open) { connection.Open(); }
                try
                {
                    //填充数据
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    //获取总记录数
                    RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                    //返回数据集
                    return ds.Tables[0];

                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                    return null; ;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Events
        private void frmMain_Load(object sender, EventArgs e)
        {
            //不自动生成列
            dgvList.AutoGenerateColumns = false;
            //绑定数据
            BindDataWithPage(1);
        }

        /// <summary>
        /// 翻页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void winFormPager1_PageIndexChanged(object sender, EventArgs e)
        {
            BindDataWithPage(winFormPager1.PageIndex);
        }
        #endregion
    }
}
